package org.huamoxi.util;

import com.alibaba.fastjson2.JSON;
import org.bukkit.Bukkit;
import org.bukkit.ChatColor;
import org.bukkit.Material;
import org.bukkit.configuration.file.FileConfiguration;
import org.bukkit.enchantments.Enchantment;
import org.bukkit.entity.Player;
import org.bukkit.inventory.Inventory;
import org.bukkit.inventory.ItemStack;
import org.bukkit.inventory.meta.EnchantmentStorageMeta;
import org.bukkit.inventory.meta.ItemMeta;
import org.huamoxi.entity.Enchant;
import org.huamoxi.entity.MarketItem;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

public class DataSQLUtil {

    public static Connection initTable(String urlPath) {
        String url = "jdbc:sqlite:" + urlPath + "marketplace.db";
        // 加载 SQLite JDBC 驱动（通常这一步是可选的，因为 SQLite JDBC 是纯 Java 实现的）
        try {
            Class.forName("org.sqlite.JDBC");
            Connection dbConnection = DriverManager.getConnection(url);
            // 执行创建表等初始化 SQL 语句
            if (dbConnection != null) {
                Statement statement = dbConnection.createStatement();
                // MarketItem 表
                String marketItemCreateSQL = "create table if not exists tb_market_item ( " +
                        "id integer primary key autoincrement," +
                        "type text not null," +
                        "title text," +
                        "lore text," +
                        "price real not null," +
                        "quantity integer not null," +
                        "durability integer," +
                        "maxDurability integer," +
                        "enchantDescription text," +
                        "category integer not null," +
                        "state integer not null," +
                        "playerUID text not null," +
                        "validity integer," +
                        "sort integer)";
                statement.executeUpdate(marketItemCreateSQL);

                // 用户分页 MarketPage 表
                String marketPageCreateSQL = "create table if not exists tb_market_page ( " +
                        "playerUID text not null, " +
                        "current integer not null," +
                        "type integer not null)";
                statement.executeUpdate(marketPageCreateSQL);

                // 离线用户收入表
                String playerIncomeRecordCreateSQL = "create table if not exists tb_income ( " +
                        "playerUID text not null," +
                        "money real not null)";
                statement.executeUpdate(playerIncomeRecordCreateSQL);
            }
            return dbConnection;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static Integer getCurrentByUID (Connection dbConnection, UUID uniqueId) {
        try {
            String getCurrentPageNumberSQL = "select current from tb_market_page where playerUID = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(getCurrentPageNumberSQL);
            preparedStatement.setString(1, uniqueId.toString());
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt("current");
            } else {
                // 未存储过就新增
                String insertPageNumberSQL = "insert into tb_market_page (playerUID, current, type) values (?, 1, 1)";
                PreparedStatement insertStmt = dbConnection.prepareStatement(insertPageNumberSQL);
                insertStmt.setString(1, uniqueId.toString());
                insertStmt.executeUpdate();
                return 1;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    public static Integer getMaxPage (Connection dbConnection, FileConfiguration config) {
        try {
            int pageSize = config.getInt("market.pageSize");
            String getMaxPageSQL = "select ceil(cast(count(*) as float) / ?) from tb_market_item";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(getMaxPageSQL);
            preparedStatement.setInt(1, pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
            return 0;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    public static BigDecimal getPriceById (Connection dbConnection, Integer id) {
        try {
            String getMaxPageSQL = "select price from tb_market_item where id = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(getMaxPageSQL);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return resultSet.getBigDecimal(1);
            }
            return BigDecimal.ZERO;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return BigDecimal.ZERO;
    }

    public static ItemStack getItemStackByMarketItenId (Connection dbConnection, Integer id) {
        try {
            String getMaxPageSQL = "select * from tb_market_item where id = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(getMaxPageSQL);
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ItemStack itemStack = new ItemStack(Material.getMaterial(resultSet.getString("type")), resultSet.getInt("quantity"));
                ItemMeta itemMeta = itemStack.getItemMeta();
                // 设置物品名称
                itemMeta.setDisplayName(resultSet.getString("title"));
                // 设置物品描述
                List<String> loreList = new ArrayList<>();
                String lore = resultSet.getString("lore");
                // 设置物品附魔
                String enchants = resultSet.getString("enchantDescription");
                setEnchants(enchants, itemStack, itemMeta);
                // 设置物品耐久
                itemStack.setDurability(resultSet.getShort("durability"));
                // 设置属性并添加到容器
                itemMeta.setLore(loreList);
                itemStack.setItemMeta(itemMeta);
                return itemStack;
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    private static void setEnchants(String enchants, ItemStack itemStack, ItemMeta itemMeta) {
        if (enchants != null && enchants.length() > 0) {
            List<Enchant> list = JSON.parseArray(enchants, Enchant.class);
            if (!list.isEmpty()) {
                if (itemStack.getType() == Material.ENCHANTED_BOOK) {
                    if (itemMeta instanceof EnchantmentStorageMeta) {
                        EnchantmentStorageMeta storageMeta = (EnchantmentStorageMeta) itemMeta;
                        for (Enchant enchant: list) {
                            Enchantment enchantment = Enchantment.getByName(enchant.getName());
                            storageMeta.addStoredEnchant(enchantment, enchant.getLevel(), true);
                        }
                    }
                } else {
                    for (Enchant enchant: list) {
                        Enchantment enchantment = Enchantment.getByName(enchant.getName());
                        itemMeta.addEnchant(enchantment, enchant.getLevel(), true);
                    }
                }
            }
        }
    }

    public static void setInventoryByCurrent(Player player, boolean isOther, Inventory inventory, FileConfiguration config, Integer current, Integer pageSize, Connection dbConnection) {
        try {
            // 查询指定页数的商品
            String pageMarketSQL;
            if (isOther) {
                pageMarketSQL = "select * from tb_market_item where playerUID != ? limit ? OFFSET ?";
            } else {
                pageMarketSQL = "select * from tb_market_item where playerUID = ? limit ? OFFSET ?";
            }
            PreparedStatement preparedStatement = dbConnection.prepareStatement(pageMarketSQL);
            preparedStatement.setString(1, player.getUniqueId().toString());
            preparedStatement.setInt(2, pageSize);
            preparedStatement.setInt(3, (current - 1) * pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            int i = 0;
            while (resultSet.next()) {
                ItemStack itemStack = new ItemStack(Material.getMaterial(resultSet.getString("type")));
                ItemMeta itemMeta = itemStack.getItemMeta();
                // 设置物品名称
                itemMeta.setDisplayName(resultSet.getString("title"));
                // 设置物品描述
                List<String> loreList = new ArrayList<>();
                loreList.add("");
                Player onLineSeller = Bukkit.getPlayer(UUID.fromString(resultSet.getString("playerUID")));
                if (onLineSeller != null) {
                    loreList.add(ChatColor.GOLD + "卖家：" + config.getString("color.playerNameColor") + onLineSeller.getDisplayName());
                } else {
                    String offlinePlayerName = Bukkit.getOfflinePlayer(UUID.fromString(resultSet.getString("playerUID"))).getName();
                    loreList.add(ChatColor.GOLD + "卖家：" + config.getString("color.playerNameColor") + offlinePlayerName);
                }

                BigDecimal price = resultSet.getBigDecimal("price");
                String priceStr = price.scale() == 0 ? String.valueOf(price.intValue()): price.toPlainString();
                loreList.add(ChatColor.GOLD + "单价：" + config.getString("color.priceColor")
                        + priceStr
                        + config.getString("market.priceSymbol"));
                loreList.add(ChatColor.GOLD + "剩余：" + config.getString("color.quantityColor")
                        + resultSet.getBigDecimal("quantity")
                        + config.getString("market.quantitySymbol"));
                // 设置物品附魔
                String enchants = resultSet.getString("enchantDescription");
                setEnchants(enchants, itemStack, itemMeta);

                // 设置物品耐久
                itemStack.setDurability(resultSet.getShort("durability"));
                // 设置属性并添加到容器
                itemMeta.setLore(loreList);
                itemStack.setItemMeta(itemMeta);
                inventory.setItem(i, itemStack);
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static int insertSQL (Connection dbConnection, String insertSQL) {
        if (dbConnection != null) {
            try {
                Statement statement = dbConnection.createStatement();
                return statement.executeUpdate(insertSQL);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public static MarketItem getMarketItemId(Connection dbConnection, Integer current, Integer pageSize, Integer rawSlot) {
        try {
            List<MarketItem> list = new ArrayList<>();
            // 查询指定页数的商品
            String pageMarketSQL = "select * from tb_market_item limit ? OFFSET ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(pageMarketSQL);
            preparedStatement.setInt(1, pageSize);
            preparedStatement.setInt(2, (current - 1) * pageSize);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                MarketItem marketItem = new MarketItem();
                marketItem.setId(resultSet.getInt("id"));
                marketItem.setPrice(resultSet.getBigDecimal("price"));
                marketItem.setQuantity(resultSet.getInt("quantity"));
                marketItem.setPlayerUID(resultSet.getString("playerUID"));
                list.add(marketItem);
            }
            if (rawSlot < list.size()) {
                MarketItem marketItem = list.get(rawSlot);
                return marketItem;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static int updateCurrentByPlayer(Connection dbConnection, Player player, Integer current) {
        try {
            String updateCurrentByPlayerSQL = "update tb_market_page set current = ? where playerUID = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(updateCurrentByPlayerSQL);
            preparedStatement.setInt(1, current);
            preparedStatement.setString(2, player.getUniqueId().toString());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static int updateTypeByPlayer(Connection dbConnection, Player player, Integer type) {
        try {
            String updateCurrentByPlayerSQL = "update tb_market_page set type = ? where playerUID = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(updateCurrentByPlayerSQL);
            preparedStatement.setInt(1, type);
            preparedStatement.setString(2, player.getUniqueId().toString());
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static void closeDatabase (Connection dbConnection){
        if (dbConnection != null) {
            try {
                dbConnection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static int deleteMarketItemById(Connection dbConnection, Integer id) {
        try {
            String deleteMarketItemByIdSQL = "delete from tb_market_item where id = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(deleteMarketItemByIdSQL);
            preparedStatement.setInt(1, id);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    public static int updateMarketItemById(Connection dbConnection, Integer id) {
        try {
            String deleteMarketItemByIdSQL = "update tb_market_item set quantity = quantity -1 where id = ?";
            PreparedStatement preparedStatement = dbConnection.prepareStatement(deleteMarketItemByIdSQL);
            preparedStatement.setInt(1, id);
            return preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 给离线玩家记录收入金额
     */
    public static int saveMoneyRecord(Connection dbConnection, UUID uid, BigDecimal money) {
        try {
            String queryRecordSQL = "select * from tb_income where playerUID = ?";
            PreparedStatement queryStmt = dbConnection.prepareStatement(queryRecordSQL);
            queryStmt.setString(1, uid.toString());
            ResultSet queryResultSet = queryStmt.executeQuery();
            if (queryResultSet.next()) {
                String updateRecordSQL = "update tb_income set money = ? where playerUID = ?";
                PreparedStatement updateStmt = dbConnection.prepareStatement(updateRecordSQL);
                updateStmt.setBigDecimal(1, queryResultSet.getBigDecimal("money").add(money));
                updateStmt.setString(2, uid.toString());
                return updateStmt.executeUpdate();
            } else {
                String saveRecordSQL = "insert into tb_income(playerUID, money) values (?, ?)";
                PreparedStatement saveStmt = dbConnection.prepareStatement(saveRecordSQL);
                saveStmt.setString(1, uid.toString());
                saveStmt.setBigDecimal(2, money);
                return saveStmt.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    /**
     * 查询离线收入
     */
    public static BigDecimal hasIncomeRecord(Connection dbConnection, UUID uid) {
        try {
            String saveMoneyRecordSQL = "select money from tb_income where playerUID = ?";
            PreparedStatement stmt = dbConnection.prepareStatement(saveMoneyRecordSQL);
            stmt.setString(1, uid.toString());
            ResultSet resultSet = stmt.executeQuery();
            if (resultSet.next()) {
                return resultSet.getBigDecimal("money");
            }
            return null;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 查询离线收入
     */
    public static boolean getTypeFromMarketPageByUID(Connection dbConnection, UUID uid) {
        try {
            String saveMoneyRecordSQL = "select type from tb_market_page where playerUID = ?";
            PreparedStatement stmt = dbConnection.prepareStatement(saveMoneyRecordSQL);
            stmt.setString(1, uid.toString());
            ResultSet resultSet = stmt.executeQuery();
            if (resultSet.next()) {
                int type = resultSet.getInt("type");
                return type == 1;
            }
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }
}
